package com.zpxt.zpxt.dao;

import com.zpxt.zpxt.entity.Application;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @Author:hjx
 * @Description:
 * @Date: Created in 23:28 2018/4/17
 * @Modified By:
 */
public interface ApplicationRepository extends JpaRepository<Application, Long> {
    @Query(
            value = "SELECT\n" +
                    "	a.id,\n" +
                    "	a.position_id,\n" +
                    "	a.resume_id,\n" +
                    "	r.talent_id \n" +
                    "FROM\n" +
                    "	application a\n" +
                    "	INNER JOIN resume r ON r.id = a.resume_id \n" +
                    "WHERE\n" +
                    "	a.position_id = ?1 \n" +
                    "	AND r.talent_id = ?2"
            , nativeQuery = true
    )
    @Modifying
    List<Map> isApplyed(Long positionId,Long talentId);

    @Query(
            value = "SELECT\n" +
                    "	a.*,\n" +
                    "	p.NAME AS position_name,\n" +
                    "	p.salary1,\n" +
                    "	p.salary2,\n" +
                    "	p.company_id,\n" +
                    "	c.NAME AS company_name,\n" +
                    "	r.intention AS resume_name,\n" +
                    "	r.talent_id \n" +
                    "FROM\n" +
                    "	application a\n" +
                    "	INNER JOIN position p ON p.id = a.position_id\n" +
                    "	INNER JOIN company c ON c.id = p.company_id\n" +
                    "	INNER JOIN resume r ON r.id = a.resume_id \n" +
                    "WHERE\n" +
                    "	r.talent_id = ?1"
            , nativeQuery = true
    )
    @Modifying
    List<Map> findAppByTalentId(Long talentId);

    @Query(
            value = "SELECT\n" +
                    "	a.*,\n" +
                    "	p.NAME AS position_name,\n" +
                    "	p.salary1,\n" +
                    "	p.salary2,\n" +
                    "	p.company_id,\n" +
                    "	c.NAME AS company_name,\n" +
                    "	r.intention AS resume_name,\n" +
                    "	r.talent_id \n" +
                    "FROM\n" +
                    "	application a\n" +
                    "	INNER JOIN position p ON p.id = a.position_id\n" +
                    "	INNER JOIN company c ON c.id = p.company_id\n" +
                    "	INNER JOIN resume r ON r.id = a.resume_id \n" +
                    "WHERE\n" +
                    "	p.company_id = ?1"
            , nativeQuery = true
    )
    @Modifying
    List<Map> findAppByCompanyId(Long companyId);
}
